﻿using System.Data;
using Npgsql;
using PureMVC.Interfaces;
using PureMVC.Patterns;

namespace SMS.Core.Model.Customer
{
    public class CustomerSearchProxy : Proxy, IProxy
    {
        #region Constants

        #region Queries

        /// <summary>
        /// Searches for customers whose names look like the one in the query.
        /// There is room for improvement with this query with the use of regexes.
        /// </summary>
        public const string QRY_SEARCH_BY_NAME =
            @"SELECT DISTINCT * 
              FROM   customer 
              WHERE  first_name LIKE ('%' || @search_string || '%')
              OR     last_name LIKE ('%' || @search_string || '%')
              OR     (first_name || ' ' || last_name) LIKE ('%' || @search_string || '%')
              ORDER BY last_name, first_name, address, cust_id;";

        /// <summary>
        /// Searches for customers whose address look like the input.
        /// Again, room for improvement with this query lies with the use of regexes.
        /// </summary>
        public const string QRY_SEARCH_BY_ADDRESS =
            @"SELECT DISTINCT *
              FROM   customer
              WHERE  address LIKE ('%' || @search_string || '%')
              ORDER BY address;";

        /// <summary>
        /// Searches for customer whose credit limit are between a specified range.
        /// </summary>
        public const string QRY_SEARCH_BY_CREDIT_LIMIT =
            @"SELECT DISTINCT *
              FROM   customer
              WHERE  credit_limit BETWEEN @l_limit AND @h_limit
              ORDER BY credit_limit;";

        #endregion

        #endregion

        #region Members

        private NpgsqlConnection m_conn;
        private NpgsqlDataReader m_dataReader;

        #region Properties

        public new NpgsqlDataReader Data
        {
            get { return m_dataReader; }
        }

        #endregion

        #endregion

        #region Constructors
        /// <summary>
        /// Constructs a <c>CustomerSearchProxy</c> with an <c>NpgsqlConnection</c> 
        /// instance. The connection instance will be used throughout the queries
        /// handled by this proxy.
        /// </summary>
        /// <param name="conn"></param>
        public CustomerSearchProxy(NpgsqlConnection conn)
        {
            m_conn = conn;
            m_dataReader = null;
        }

        #endregion

        #region Public Methods

        public NpgsqlDataReader SearchByName(string searchString)
        {
            m_conn.Open();
            var searchCmd = new NpgsqlCommand(QRY_SEARCH_BY_NAME, m_conn);
            searchCmd.Parameters.Add("search_string", searchString);
            m_dataReader = searchCmd.ExecuteReader(CommandBehavior.CloseConnection);
            return Data;
        }

        public NpgsqlDataReader SearchByNameSingle(string searchString)
        {
            m_conn.Open();
            var searchCmd = new NpgsqlCommand(QRY_SEARCH_BY_NAME, m_conn);
            searchCmd.Parameters.Add("search_string", searchString);
            m_dataReader = searchCmd.ExecuteReader(CommandBehavior.SingleRow);
            m_conn.Close();
            return Data;
        }

        public NpgsqlDataReader SearchByAddress(string searchString)
        {
            m_conn.Open();
            var searchCmd = new NpgsqlCommand(QRY_SEARCH_BY_ADDRESS, m_conn);
            searchCmd.Parameters.Add("search_string", searchString);
            m_dataReader =  searchCmd.ExecuteReader(CommandBehavior.CloseConnection);
            return Data;
        }

        public NpgsqlDataReader SearchByCreditLimit(decimal l_limit, decimal h_limit)
        {
            m_conn.Open();
            var searchCmd = new NpgsqlCommand(QRY_SEARCH_BY_CREDIT_LIMIT, m_conn);
            searchCmd.Parameters.Add("l_limit", l_limit);
            searchCmd.Parameters.Add("h_limit", h_limit);
            m_dataReader = searchCmd.ExecuteReader(CommandBehavior.CloseConnection);
            return Data;
        }

        #endregion
    }
}